package Jcat.cduestcbbs.DaoImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jcat.hbc.connectionpool.HBCConnectionPool;
import Jcat.cduestcbbs.Dao.UserDao;
import Jcat.cduestcbbs.Po.UserPo;
import Jcat.cduestcbbs.tools.JdbcTemplate;

public class UserDaoImpl implements UserDao {

	public boolean deleteUser(int userId) throws SQLException {
		return false;
//		JdbcTemplate template = new JdbcTemplate();
//		boolean delete = template
//				.delete("delete from person where person_id=6");
//		return delete;
	}

	public boolean insertUser(UserPo user) throws SQLException {
		Connection con = HBCConnectionPool.getInstance().getConnection();
		Statement st = null;
		st = con.createStatement();
		int i = 0;
		try {
			i = st
					.executeUpdate("insert into person(person_name,person_password,person_email,"
							+ "person_safe_question,person_safe_question_answer,person_nickname,person_number,person_sex, person_from,"
							+ "person_communcation,person_self_introduction) "
							+ "values" + "('"
							+ user.getUserName()
							+ "','"
							+ user.getUserPassword()
							+ "','"
							+ user.getUserEmail()
							+ "',"
							+ "'"
							+ user.getUserSafeQuestion()
							+ "','"
							+ user.getUserSafeQuestionAnswer()
							+ "','"
							+ user.getUserNickName()
							+ "',"
							+ "'"
							+ user.getUserNumber()
							+ "','"
							+ user.getUserSex()
							+ "','"
							+ user.getUserAdrress()
							+ "',"
							+ "'"
							+ user.getUserCommuncation()
							+ "','"
							+ user.getUserSelfIntrodustion() + "')");
		}

		finally {
			con.close();
		}
		if (i == 1) {
			return true;
		} else {

			return false;
		}
	}

	public UserPo selectUserByUserName(UserPo user) throws SQLException {
		Connection con = HBCConnectionPool.getInstance().getConnection();
		Statement st = null;
		ResultSet rs = null;
		st = con.createStatement();
		rs = st.executeQuery("select * from person where person_name='"
				+ user.getUserName() + "'");
		try {
			while (rs.next()) {

				user.setUserName((rs.getString("person_name")));
				user.setUserPassword((rs.getString("person_password")));

			}
		} finally {
			con.close();
		}
		return user;

	}

	public boolean updateUser(UserPo user) throws SQLException {
		return false;
	}

	public List<UserPo> userQuery() throws SQLException {
		return null;
	}

	public UserPo selectNewUser() throws SQLException {

		Connection con = HBCConnectionPool.getInstance().getConnection();
		UserPo user = null;
		Statement st = null;
		ResultSet rs = null;
		st = con.createStatement();
		rs = st
				.executeQuery("select person_name from person order by person_id limit 0,1");
		try {
			while (rs.next()) {
				user = new UserPo();
				user.setUserName(rs.getString("person_name"));

			}
		} finally {
			con.close();
		}
		return user;
	}

	public int getAllUserNumbers() throws SQLException {

		int count = 0;
		Connection con = HBCConnectionPool.getInstance().getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {

			st = con.createStatement();

			rs = st.executeQuery("select count(*) from person");
			while (rs.next()) {
				count = rs.getInt(1);

			}
		} finally {
			con.close();
		}

		return count;
	}

	public UserPo selectUserByuserId(int id) throws SQLException {

		Connection con = HBCConnectionPool.getInstance().getConnection();
		UserPo user = null;
		Statement st = null;
		ResultSet rs = null;
		st = con.createStatement();
		rs = st
				.executeQuery("select person_id,person_nickname,person_number,person_sex,person_email,person_from,person_register_time,person_self_introduction,person_right from person where person_id= "
						+ id + "");
		try {
			while (rs.next()) {
				user = new UserPo();
				user.setUserNickName(rs.getString(2));
				user.setUserSex(rs.getString(4));
				user.setUserId(rs.getInt(1));

				user.setUserNumber(rs.getString(3));
				user.setUserAdrress(rs.getString(6));
				user.setUserEmail(rs.getString(5));
				user.setUserRegisterTime(rs.getString(7));
				user.setUserSelfIntrodustion(rs.getString(8));
				user.setUserRight(rs.getInt(9));
			}
		} finally {
			con.close();
		}
		return user;
	}

	public List<UserPo> getuserPaingList(String searchWay, int pageNumber,
			String orderBy) throws SQLException {
		List<UserPo> userPagingList = new ArrayList<UserPo>();
		Connection con = HBCConnectionPool.getInstance().getConnection();
		UserPo user = null;
		int start = (pageNumber - 1) * 40;
		int end = 40;

		Statement st = null;
		ResultSet rs = null;
		st = con.createStatement();
		String sql = "select person_id,person_name,person_number,person_sex,person_management_place,person_register_time from person order by '"
				+ orderBy + "'  limit " + start + " , " + end + "";
		rs = st.executeQuery(sql);
		try {
			while (rs.next()) {
				user = new UserPo();
				user.setUserId(rs.getInt(1));
				user.setUserName(rs.getString(2));
				user.setUserNumber(rs.getString(3));
				user.setUserSex(rs.getString(4));
				user.setUserManagementPlace(rs.getString(5));
				user.setUserRegisterTime(rs.getString(6));
				userPagingList.add(user);
			}
		} finally {
			con.close();
		}
		return userPagingList;
	}

	public List<UserPo> getUserByConditions(String searchWay, Object o)
			throws SQLException {
		List<UserPo> userForConditionsList = new ArrayList<UserPo>();
		Connection con = HBCConnectionPool.getInstance().getConnection();
		UserPo user = null;
		Statement st = null;
		ResultSet rs = null;
		st = con.createStatement();
		String sql = "select person_id,person_name,person_number,person_sex,person_management_place,person_register_time from person   where "
				+ searchWay + "='" + o + "'";
		rs = st.executeQuery(sql);
		try {
			while (rs.next()) {
				user = new UserPo();
				user.setUserId(rs.getInt(1));
				user.setUserName(rs.getString(2));
				user.setUserNumber(rs.getString(3));
				user.setUserSex(rs.getString(4));
				user.setUserManagementPlace(rs.getString(5));
				user.setUserRegisterTime(rs.getString(6));
				userForConditionsList.add(user);
			}
		} finally {
			con.close();
		}
		return userForConditionsList;
	}

}
